package com.alex.goods.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DBUtil {

	private static DataSource dataSource;

	static {
		Properties ps = new Properties();
		try {
			ps.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
			dataSource = DruidDataSourceFactory.createDataSource(ps);

		} catch (Exception e) {
			System.out.println("初始化数据库连接池失败");
			e.printStackTrace();
		}
	}

	public static int excuteDMLs(String[] sql, Object[][] params) {
		Connection conn = null;
		PreparedStatement ps = null;
		int count = 0;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false); // to make the some sqls can  execute for one time 
			for (int i = 0; i < sql.length; i++) {
				ps = conn.prepareStatement(sql[i]);
				if (params[i] != null) {
					for (int j = 0; j < params[i].length; j++) {
						ps.setObject(j + 1, params[i][j]);
					}
				}
				count += ps.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				count = -1;
				conn.rollback();
			} catch (SQLException e1) {

			}
			System.out.println("excute DML failure");
		} finally {
			try {
				conn.setAutoCommit(true);     // 
			} catch (SQLException e) {

			}
			DBUtil.close(ps, conn);
		}
		return count;
	}

	/**
	 * 功能：下划线命名转驼峰命名 将下划线替换为空格,将字符串根据空格分割成数组,再将每个单词首字母大写
	 */

	public static String underLineToHump(String name) {
		if (name == null || name.indexOf('_') < 0) {
			return name;
		}

		String[] arr = name.split("_");

		StringBuilder sb = new StringBuilder(arr[0]);

		for (int i = 1; i < arr.length; i++) {
			sb.append(arr[i].substring(0, 1).toUpperCase());
			sb.append(arr[i].substring(1));
		}
		return sb.toString();
	}

	public static int excuteDML(String sql, Object... params) {

		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println("excute DML failure");
		} finally {
			DBUtil.close(ps, conn);
		}
		return -1;
	}

	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	public static <T> List<T> executeDQL(String sql, Class<T> c, Object... params) {
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();

			while (rs.next()) {
				T obj = c.newInstance();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {

					Object value = rs.getObject(i);
					String label = rsmd.getColumnLabel(i);

					label = underLineToHump(label); // make the emp_no to empNo to match the entity class's value's
													// title
					try {

						Field field = c.getDeclaredField(label);  // field is type of class's  x's qualified name  
						field.setAccessible(true);
						field.set(obj, value);

					} catch (Exception e) {
						System.out.println(label + ": 这一列没找到 ");
					}
				}
				list.add(obj);
			}
		} catch (Exception e) {

		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}

	public static void close(PreparedStatement ps, Connection conn) {
		DBUtil.close(null, ps, conn);
	}
	public static Long executeCount(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			rs.next(); // point the res's first line
			return rs.getLong(1);
		} catch (Exception e) {
			// TODO: handle exception
			return -1L;
		} finally {
			DBUtil.close(rs, ps, conn);
		}
	}
}
